import pandas as pd
import re
import colorParser as cp

base_dir = "data"
# 输入
src_path = f"{base_dir}/data-src.xls"
src_xls = pd.ExcelFile(src_path)
dfs = {}
for name in src_xls.sheet_names:
    df = src_xls.parse(sheet_name=name)
    dfs[name] = df
df_item = dfs['item']
cp.init(dfs['colorcode'])
'''
需要读取的字段：CustomsType,zhName,enName,Colors,Sizes,,Price,Url,OpponentUrl,Length,Width,Height,Weight
'''
whole_out_columns = [
    'number', 'cate_id', 'cn_name', 'en_name', 'tag', 'specifications', 'packing', 'Suffix', 'declare_cn_name', 'declare_en_name', 'original_price', 'purchase_link',
    'competitor_link', 'length', 'width', 'height', 'weight', 'lock_up_platform', 'label', 'brand_authorize', 'remark', 'attribute', 'specification_detail',
    'shipping_list', 'supplier_name', 'supplier_phone', 'qq', 'address', 'art_designer', 'supplier_product', 'platform'
]
# 输出
out_path = f"{base_dir}/erptemplate.xlsx"
df_out = pd.DataFrame(columns=whole_out_columns)

# 声明诸多缓存变量
SPUAliases = []
allNumbers = []
allzhNames = []
allSuffixes = []

spuenNames = {}
spuOriginalprice = {}
spuPurchaselink = {}
spuLength = {}
spuWidth = {}
spuHeight = {}
spuWeight = {}
spuSpecialdetail = {}

#碎花模式匹配
floralPat = re.compile(r'FL(\d)N')
spuC = 1  # SPU计数
for index, row in df_item.iterrows():
    spuAlias = 'A' + str(spuC).zfill(3)  # spu别名
    SPUAliases.append(spuAlias)
    zhName = row['zhName']
    enName = row['enName']
    spuenNames[spuAlias] = enName
    spuOriginalprice[spuAlias] = row['Price']
    spuPurchaselink[spuAlias] = row['Url']
    spuLength[spuAlias] = row['Length']
    spuWidth[spuAlias] = row['Width']
    spuHeight[spuAlias] = row['Height']
    spuWeight[spuAlias] = row['Weight']
    spuSpecialdetail[spuAlias] = enName

    colorStr = row['Colors']
    sizeStr = row['Sizes']
    colorList = colorStr.split(',')
    sizeList = sizeStr.split(',')

    for color in colorList:
        print(color)
        for size in sizeList:
            allNumbers.append(spuAlias)
            allSuffixes.append(f"{color}{size}")
            zhNameSKU = ''

            # 获取颜色代码
            color_zhname = cp.getVal(color, 'zhname')
            if color_zhname != None:
                zhNameSKU = f"{zhName}-{color_zhname}{size}"
            else:
                print(f'未知颜色代码！code={color}')
                exit()
            allzhNames.append(zhNameSKU)

    spuC = spuC + 1

# 赋值给输出df
# 先处理每行各不同的列
df_out['number'] = allNumbers
df_out['cate_id'] = ''
df_out['cn_name'] = allzhNames
df_out['tag'] = '普货'
df_out['specifications'] = ''
df_out['packing'] = ''
df_out['Suffix'] = allSuffixes
df_out['declare_cn_name'] = '女士上衣'
df_out['declare_en_name'] = 'women tops'
df_out['lock_up_platform'] = ''
df_out['label'] = 'A'
df_out['brand_authorize'] = 0
df_out['remark'] = ''
df_out['attribute'] = ''
df_out['shipping_list'] = ''
df_out['supplier_name'] = ''
df_out['supplier_phone'] = ''
df_out['qq'] = ''
df_out['address'] = ''
df_out['art_designer'] = ''
df_out['supplier_product'] = 0
df_out['platform'] = 'walmart'

for alias in SPUAliases:
    df_out.loc[df_out['number'] == alias, 'en_name'] = spuenNames[alias]
    df_out.loc[df_out['number'] == alias, 'original_price'] = spuOriginalprice[alias]
    df_out.loc[df_out['number'] == alias, 'purchase_link'] = spuPurchaselink[alias]
    df_out.loc[df_out['number'] == alias, 'competitor_link'] = spuPurchaselink[alias]  # 竞争者链接用购买链接代替
    df_out.loc[df_out['number'] == alias, 'length'] = spuLength[alias]
    df_out.loc[df_out['number'] == alias, 'width'] = spuWidth[alias]
    df_out.loc[df_out['number'] == alias, 'height'] = spuHeight[alias]
    df_out.loc[df_out['number'] == alias, 'weight'] = spuWeight[alias]
    df_out.loc[df_out['number'] == alias, 'specification_detail'] = spuSpecialdetail[alias]

# 输出结果
df_out.to_excel(out_path, index=False)
